using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace ASPNETVN.PORTAL.Components.Security
{
    public partial class User
    {
        #region Properties.

        public int ID { set; get; }
        public Guid PortalID { set; get; }
        public string Name { set; get; }
        public string Username { set; get; }
        public string Email { set; get; }
        public string Password { set; get; }
        public bool Gender { set; get; }
        public string Address { set; get; }
        public string City { set; get; }
        public string Country { set; get; }
        public string Phone { set; get; }
        public string AvatarImagePath { set; get; }
        public string Status { set; get; }
        public DateTime JoinedTime { set; get; }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Methods
        protected static IList<User> ConvertToCollection(IDataReader reader)
        {
            IList<User> collection = new List<User>();
            while (reader.Read())
            {
                User entity = new User();
                if (!reader.IsDBNull(reader.GetOrdinal("ID"))) entity.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                if (!reader.IsDBNull(reader.GetOrdinal("PortalID"))) entity.PortalID = reader.GetGuid(reader.GetOrdinal("PortalID"));
                if (!reader.IsDBNull(reader.GetOrdinal("Name"))) entity.Name = reader.GetString(reader.GetOrdinal("Name"));
                if (!reader.IsDBNull(reader.GetOrdinal("Username"))) entity.Username = reader.GetString(reader.GetOrdinal("Username"));
                if (!reader.IsDBNull(reader.GetOrdinal("Email"))) entity.Email = reader.GetString(reader.GetOrdinal("Email"));
                if (!reader.IsDBNull(reader.GetOrdinal("Password"))) entity.Password = reader.GetString(reader.GetOrdinal("Password"));
                if (!reader.IsDBNull(reader.GetOrdinal("Gender"))) entity.Gender = reader.GetBoolean(reader.GetOrdinal("Gender"));
                if (!reader.IsDBNull(reader.GetOrdinal("Address"))) entity.Address = reader.GetString(reader.GetOrdinal("Address"));
                if (!reader.IsDBNull(reader.GetOrdinal("City"))) entity.City = reader.GetString(reader.GetOrdinal("City"));
                if (!reader.IsDBNull(reader.GetOrdinal("Country"))) entity.Country = reader.GetString(reader.GetOrdinal("Country"));
                if (!reader.IsDBNull(reader.GetOrdinal("Phone"))) entity.Phone = reader.GetString(reader.GetOrdinal("Phone"));
                if (!reader.IsDBNull(reader.GetOrdinal("AvatarImagePath"))) entity.AvatarImagePath = reader.GetString(reader.GetOrdinal("AvatarImagePath"));
                if (!reader.IsDBNull(reader.GetOrdinal("Status"))) entity.Status = reader.GetString(reader.GetOrdinal("Status"));
                if (!reader.IsDBNull(reader.GetOrdinal("JoinedTime"))) entity.JoinedTime = reader.GetDateTime(reader.GetOrdinal("JoinedTime"));
                collection.Add(entity);
            }
            reader.Close();
            return collection;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Select methods.

        public static User Load(int id)
        {
            const string spName = "[dbo].[p_SYSTEM_User_Load]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, id);
            IDataReader reader = db.ExecuteReader(dbCommand);
            IList<User> collection = ConvertToCollection(reader);
            if (collection.Count > 0)
            {
                return collection[0];
            }
            return null;
        }

        //---------------------------------------------------------------------------------------------
        public static IList<User> SelectCollectionAll()
        {
            IDataReader reader = SelectReaderAll();
            return ConvertToCollection(reader);
        }

        //---------------------------------------------------------------------------------------------

        public static IList<User> SelectCollectionDynamic(string whereCondition, string orderByExpression)
        {
            IDataReader reader = SelectReaderDynamic(whereCondition, orderByExpression);
            return ConvertToCollection(reader);
        }

        //---------------------------------------------------------------------------------------------

        // Select by foreign key return collection		


        public static DataSet SelectAll()
        {
            const string spName = "[dbo].[p_SYSTEM_User_SelectAll]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);


            return db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static DataSet SelectDynamic(string whereCondition, string orderByExpression)
        {
            const string spName = "[dbo].[p_SYSTEM_User_SelectDynamic]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static IDataReader SelectReaderAll()
        {
            const string spName = "[dbo].[p_SYSTEM_User_SelectAll]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            return db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public static IDataReader SelectReaderDynamic(string whereCondition, string orderByExpression)
        {
            const string spName = "[dbo].[p_SYSTEM_User_SelectDynamic]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        // Select by foreign key return collection		

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert methods.

        public static int InsertUser(Guid portalID, string name, string username, string email, string password, bool gender, string address, string city, string country, string phone, string avatarImagePath, string status, DateTime joinedTime)
        {
            User entity = new User();
            entity.PortalID = portalID;
            entity.Name = name;
            entity.Username = username;
            entity.Email = email;
            entity.Password = password;
            entity.Gender = gender;
            entity.Address = address;
            entity.City = city;
            entity.Country = country;
            entity.Phone = phone;
            entity.AvatarImagePath = avatarImagePath;
            entity.Status = status;
            entity.JoinedTime = joinedTime;
            return entity.Insert();
        }

        public int Insert()
        {
            return this.Insert(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Insert(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_SYSTEM_User_Insert]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddOutParameter(dbCommand, "@ID", SqlDbType.Int, 4);
            db.AddInParameter(dbCommand, "@PortalID", SqlDbType.UniqueIdentifier, PortalID);
            db.AddInParameter(dbCommand, "@Name", SqlDbType.NVarChar, Name);
            db.AddInParameter(dbCommand, "@Username", SqlDbType.VarChar, Username);
            db.AddInParameter(dbCommand, "@Email", SqlDbType.VarChar, Email);
            db.AddInParameter(dbCommand, "@Password", SqlDbType.VarChar, Password);
            db.AddInParameter(dbCommand, "@Gender", SqlDbType.Bit, Gender);
            db.AddInParameter(dbCommand, "@Address", SqlDbType.NVarChar, Address);
            db.AddInParameter(dbCommand, "@City", SqlDbType.NVarChar, City);
            db.AddInParameter(dbCommand, "@Country", SqlDbType.VarChar, Country);
            db.AddInParameter(dbCommand, "@Phone", SqlDbType.VarChar, Phone);
            db.AddInParameter(dbCommand, "@AvatarImagePath", SqlDbType.NVarChar, AvatarImagePath);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.VarChar, Status);
            db.AddInParameter(dbCommand, "@JoinedTime", SqlDbType.DateTime, JoinedTime.Year <= 1753 ? DBNull.Value : (object)JoinedTime);

            if (transaction != null)
            {
                db.ExecuteNonQuery(dbCommand, transaction);
                ID = (int)db.GetParameterValue(dbCommand, "@ID");
                return ID;
            }
            else
            {
                db.ExecuteNonQuery(dbCommand);
                ID = (int)db.GetParameterValue(dbCommand, "@ID");
                return ID;
            }
        }

        //---------------------------------------------------------------------------------------------
        public static bool InsertCollection(IList<User> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (User item in collection)
                        {
                            if (item.Insert(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at InsertCollection method: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert / Update methods.

        public static int InsertUpdateUser(int id, Guid portalID, string name, string username, string email, string password, bool gender, string address, string city, string country, string phone, string avatarImagePath, string status, DateTime joinedTime)
        {
            User entity = new User();
            entity.ID = id;
            entity.PortalID = portalID;
            entity.Name = name;
            entity.Username = username;
            entity.Email = email;
            entity.Password = password;
            entity.Gender = gender;
            entity.Address = address;
            entity.City = city;
            entity.Country = country;
            entity.Phone = phone;
            entity.AvatarImagePath = avatarImagePath;
            entity.Status = status;
            entity.JoinedTime = joinedTime;
            return entity.InsertUpdate();
        }

        //---------------------------------------------------------------------------------------------

        public int InsertUpdate()
        {
            return this.InsertUpdate(null);
        }

        //---------------------------------------------------------------------------------------------

        public int InsertUpdate(SqlTransaction transaction)
        {
            const string spName = "p_SYSTEM_User_InsertUpdate";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, ID);
            db.AddInParameter(dbCommand, "@PortalID", SqlDbType.UniqueIdentifier, PortalID);
            db.AddInParameter(dbCommand, "@Name", SqlDbType.NVarChar, Name);
            db.AddInParameter(dbCommand, "@Username", SqlDbType.VarChar, Username);
            db.AddInParameter(dbCommand, "@Email", SqlDbType.VarChar, Email);
            db.AddInParameter(dbCommand, "@Password", SqlDbType.VarChar, Password);
            db.AddInParameter(dbCommand, "@Gender", SqlDbType.Bit, Gender);
            db.AddInParameter(dbCommand, "@Address", SqlDbType.NVarChar, Address);
            db.AddInParameter(dbCommand, "@City", SqlDbType.NVarChar, City);
            db.AddInParameter(dbCommand, "@Country", SqlDbType.VarChar, Country);
            db.AddInParameter(dbCommand, "@Phone", SqlDbType.VarChar, Phone);
            db.AddInParameter(dbCommand, "@AvatarImagePath", SqlDbType.NVarChar, AvatarImagePath);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.VarChar, Status);
            db.AddInParameter(dbCommand, "@JoinedTime", SqlDbType.DateTime, JoinedTime.Year <= 1753 ? DBNull.Value : (object)JoinedTime);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------
        public static bool InsertUpdateCollection(IList<User> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (User item in collection)
                        {
                            if (item.InsertUpdate(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at InsertUpdateCollection method: " + ex.Message);

                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }
        #endregion

        //---------------------------------------------------------------------------------------------

        #region Update methods.

        public static int UpdateUser(int id, Guid portalID, string name, string username, string email, string password, bool gender, string address, string city, string country, string phone, string avatarImagePath, string status, DateTime joinedTime)
        {
            User entity = new User();
            entity.ID = id;
            entity.PortalID = portalID;
            entity.Name = name;
            entity.Username = username;
            entity.Email = email;
            entity.Password = password;
            entity.Gender = gender;
            entity.Address = address;
            entity.City = city;
            entity.Country = country;
            entity.Phone = phone;
            entity.AvatarImagePath = avatarImagePath;
            entity.Status = status;
            entity.JoinedTime = joinedTime;
            return entity.Update();
        }

        //---------------------------------------------------------------------------------------------

        public int Update()
        {
            return this.Update(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Update(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_SYSTEM_User_Update]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, ID);
            db.AddInParameter(dbCommand, "@PortalID", SqlDbType.UniqueIdentifier, PortalID);
            db.AddInParameter(dbCommand, "@Name", SqlDbType.NVarChar, Name);
            db.AddInParameter(dbCommand, "@Username", SqlDbType.VarChar, Username);
            db.AddInParameter(dbCommand, "@Email", SqlDbType.VarChar, Email);
            db.AddInParameter(dbCommand, "@Password", SqlDbType.VarChar, Password);
            db.AddInParameter(dbCommand, "@Gender", SqlDbType.Bit, Gender);
            db.AddInParameter(dbCommand, "@Address", SqlDbType.NVarChar, Address);
            db.AddInParameter(dbCommand, "@City", SqlDbType.NVarChar, City);
            db.AddInParameter(dbCommand, "@Country", SqlDbType.VarChar, Country);
            db.AddInParameter(dbCommand, "@Phone", SqlDbType.VarChar, Phone);
            db.AddInParameter(dbCommand, "@AvatarImagePath", SqlDbType.NVarChar, AvatarImagePath);
            db.AddInParameter(dbCommand, "@Status", SqlDbType.VarChar, Status);
            db.AddInParameter(dbCommand, "@JoinedTime", SqlDbType.DateTime, JoinedTime.Year <= 1753 ? DBNull.Value : (object)JoinedTime);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------
        public static bool UpdateCollection(IList<User> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (User item in collection)
                        {
                            if (item.Update(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at UpdateCollection method: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Delete methods.

        public static int DeleteUser(int id)
        {
            User entity = new User();
            entity.ID = id;

            return entity.Delete();
        }

        public int Delete()
        {
            return this.Delete(null);
        }

        //---------------------------------------------------------------------------------------------

        public int Delete(SqlTransaction transaction)
        {
            const string spName = "[dbo].[p_SYSTEM_User_Delete]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@ID", SqlDbType.Int, ID);

            if (transaction != null)
                return db.ExecuteNonQuery(dbCommand, transaction);
            else
                return db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------


        public static int DeleteDynamic(string whereCondition)
        {
            const string spName = "[dbo].[p_SYSTEM_User_DeleteDynamic]";
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            SqlCommand dbCommand = (SqlCommand)db.GetStoredProcCommand(spName);

            db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);

            return db.ExecuteNonQuery(dbCommand);
        }
        //---------------------------------------------------------------------------------------------

        public static bool DeleteCollection(IList<User> collection)
        {
            bool ret;
            SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        bool ret01 = true;
                        foreach (User item in collection)
                        {
                            if (item.Delete(transaction) <= 0)
                            {
                                ret01 = false;
                                break;
                            }
                        }
                        if (ret01)
                        {
                            transaction.Commit();
                            ret = true;
                        }
                        else
                        {
                            transaction.Rollback();
                            ret = false;
                        }
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        throw new Exception("Error at DeleteCollection method: " + ex.Message);
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
            return ret;
        }
        #endregion
    }
}